﻿Imports System.Data.OleDb

Module XL_No
    Structure TienNo
        Private _iD As Integer
        Property ID() As Integer
            Get
                Return _iD
            End Get
            Set(ByVal value As Integer)
                _iD = value
            End Set
        End Property
        Private _nguoiDung As Integer
        Property NguoiDung() As Integer
            Get
                Return _nguoiDung
            End Get
            Set(ByVal value As Integer)
                _nguoiDung = value
            End Set
        End Property
        Private _ngayBatDau As Date
        Property NgayBatDau() As Date
            Get
                Return _ngayBatDau
            End Get
            Set(ByVal value As Date)
                _ngayBatDau = value
            End Set
        End Property
        Private _lanTraCuoi As Date
        Property LanTraCuoi() As Date
            Get
                Return _lanTraCuoi
            End Get
            Set(ByVal value As Date)
                _lanTraCuoi = value
            End Set
        End Property
        Private _soTien As Integer
        Property SoTien() As Integer
            Get
                Return _soTien
            End Get
            Set(ByVal value As Integer)
                _soTien = value
            End Set
        End Property
        Private _loai As Boolean
        Property Loai() As Boolean
            Get
                Return _loai
            End Get
            Set(ByVal value As Boolean)
                _loai = value
            End Set
        End Property
        Private _thoiHan As Integer
        Property ThoiHan() As Integer
            Get
                Return _thoiHan
            End Get
            Set(ByVal value As Integer)
                _thoiHan = value
            End Set
        End Property
        Private _laiSuat As Double
        Property LaiSuat() As Double
            Get
                Return _laiSuat
            End Get
            Set(ByVal value As Double)
                _laiSuat = value
            End Set
        End Property
        Private _daTra As Integer
        Property DaTra() As Integer
            Get
                Return _daTra
            End Get
            Set(ByVal value As Integer)
                _daTra = value
            End Set
        End Property
        Private _chuNo As String
        Property ChuNo() As String
            Get
                Return _chuNo
            End Get
            Set(ByVal value As String)
                _chuNo = value
            End Set
        End Property
        Private _noiDung As String
        Property NoiDung() As String
            Get
                Return _noiDung
            End Get
            Set(ByVal value As String)
                _noiDung = value
            End Set
        End Property
    End Structure

    Public Function LayDS(ByVal ngdung As Integer) As List(Of TienNo)
        Dim ds As New List(Of TienNo)
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from TienNo where NguoiDung=@NguoiDung", con)
            cmd.Parameters.AddWithValue("@NguoiDung", ngdung)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim p As TienNo = New TienNo()
                p.ID = rd.GetInt32(0)
                p.NguoiDung = rd.GetInt32(1)
                p.NgayBatDau = rd.GetDateTime(2)
                p.LanTraCuoi = rd.GetDateTime(3)
                p.SoTien = rd.GetInt32(4)
                p.Loai = rd.GetBoolean(5)
                p.ThoiHan = rd.GetInt32(6)
                p.LaiSuat = rd.GetDouble(7)
                p.DaTra = rd.GetInt32(8)
                p.ChuNo = rd.GetString(9)
                p.NoiDung = rd.GetString(10)
                ds.Add(p)
            End While
            con.Close()
            Return (ds)
        Catch ex As Exception
            Return ds
        End Try
    End Function

    Public Function Lay(ByVal id As Integer) As TienNo
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from TienNo where ID=@ID", con)
            cmd.Parameters.AddWithValue("@ID", id)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Dim p As TienNo = New TienNo()
            If rd.Read() Then
                p.ID = rd.GetInt32(0)
                p.NguoiDung = rd.GetInt32(1)
                p.NgayBatDau = rd.GetDateTime(2)
                p.LanTraCuoi = rd.GetDateTime(3)
                p.SoTien = rd.GetInt32(4)
                p.Loai = rd.GetBoolean(5)
                p.ThoiHan = rd.GetInt32(6)
                p.LaiSuat = rd.GetDouble(7)
                p.DaTra = rd.GetInt32(8)
                p.ChuNo = rd.GetString(9)
                p.NoiDung = rd.GetString(10)
            End If
            con.Close()
            Return p
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Them(ByRef p As TienNo) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("insert into TienNo(NguoiDung, NgayBatDau, LanTraCuoi, SoTien, Loai, ThoiHan, LaiSuat, DaTra, ChuNo, NoiDung) values(@NguoiDung, @NgayBatDau, @LanTraCuoi, @SoTien, @Loai, @ThoiHan, @LaiSuat, @DaTra, @ChuNo, @NoiDung)", con)
            cmd.Parameters.AddWithValue("@NguoiDung", p.NguoiDung)
            cmd.Parameters.AddWithValue("@NgayBatDau", p.NgayBatDau)
            cmd.Parameters.AddWithValue("@LanTraCuoi", p.LanTraCuoi)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@Loai", p.Loai)
            cmd.Parameters.AddWithValue("@ThoiHan", p.ThoiHan)
            cmd.Parameters.AddWithValue("@LaiSuat", p.LaiSuat)
            cmd.Parameters.AddWithValue("@DaTra", p.DaTra)
            cmd.Parameters.AddWithValue("@ChuNo", p.ChuNo)
            cmd.Parameters.AddWithValue("@NoiDung", p.NoiDung)
            Dim kq = cmd.ExecuteNonQuery()
            cmd.CommandText = "select @@IDENTITY"
            p.ID = cmd.ExecuteScalar()

            'Them chi tu dong
            'Dim chi As ChiCoDinh
            'chi.Ngay = p.NgayBatDau.Day
            'chi.ChiTiet = "Tra no"
            'chi.LanCuoi = p.NgayBatDau
            'chi.NguoiChi = p.NguoiDung
            'If p.Loai = False Then
            '    chi.SoTien = p.SoTien * p.LaiSuat / 100
            'Else
            'chi.SoTien()
            'End If
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Xoa(ByVal p As TienNo) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("delete from TienNo where ID = @ID", con)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function CapNhat(ByVal p As TienNo) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update TienNo set NguoiDung=@NguoiDung, NgayBatDau=@NgayBatDau,LanTraCuoi=@LanTraCuoi, SoTien=@SoTien, Loai=@Loai, ThoiHan=@ThoiHan, LaiSuat=@LaiSuat, DaTra=@DaTra, ChuNo=@ChuNo, NoiDung=@NoiDung where ID = @ID", con)
            cmd.Parameters.AddWithValue("@NguoiDung", p.NguoiDung)
            cmd.Parameters.AddWithValue("@NgayBatDau", p.NgayBatDau)
            cmd.Parameters.AddWithValue("@LanTraCuoi", p.LanTraCuoi)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@Loai", p.Loai)
            cmd.Parameters.AddWithValue("@ThoiHan", p.ThoiHan)
            cmd.Parameters.AddWithValue("@LaiSuat", p.LaiSuat)
            cmd.Parameters.AddWithValue("@DaTra", p.DaTra)
            cmd.Parameters.AddWithValue("@ChuNo", p.ChuNo)
            cmd.Parameters.AddWithValue("@NoiDung", p.NoiDung)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function PhatSinhPhieuChi(ByVal user As Integer) As Integer
        Try
            Dim count As Integer = 0
            Dim ds = XL_No.LayDS(user)
            For Each n In ds
                If n.DaTra < n.SoTien Then
                    While DateDiff(DateInterval.Month, n.LanTraCuoi, Now.Date) > 0
                        Dim p As New PhieuChi
                        n.LanTraCuoi = n.LanTraCuoi.AddMonths(1)
                        p.Ngay = n.LanTraCuoi
                        p.NguoiChi = n.NguoiDung
                        p.ChiTiet = "Trả nợ"
                        p.NguoiLap = n.NguoiDung
                        If n.Loai = False Then
                            p.SoTien = n.SoTien * n.LaiSuat * 100
                        Else
                            Dim datra = DateDiff(DateInterval.Month, n.NgayBatDau, n.LanTraCuoi)
                            p.SoTien = (n.SoTien / n.ThoiHan) + (n.SoTien - (datra - 1) * (n.SoTien / n.ThoiHan)) * n.LaiSuat / 100
                            n.DaTra += n.SoTien / n.ThoiHan
                        End If
                        XL_No.CapNhat(n)
                        p.LoaiChi = 0
                        p.TaiKhoan = 0
                        XL_PhieuChi.Them(p)
                        count += 1
                    End While
                End If
            Next
            Return count
        Catch ex As Exception
            Return -1
        End Try
    End Function
End Module
